package com.example.yuntiku.dao.impl;

import com.example.yuntiku.dao.BaseDao;
import com.example.yuntiku.dao.QuestionDao;
import com.example.yuntiku.entity.Question;
import com.example.yuntiku.entity.Record;
import com.example.yuntiku.entity.Testrecord;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class QuestionDaoImpl extends BaseDao implements QuestionDao {

    public List<Question> selectQuestion(String[] recordlist, String section, String knowledgeone, String knowledgetwo, int tmshu){
        List<Question> list = new ArrayList<>();
        ResultSet rs = null;
        List<Object> params = new ArrayList<>();
        try {

            String sql="SELECT `id`,`content`,`option_a`,`option_b`,`option_c`,`option_d`,`answer`,`resolution`,\n" +
                    "`praise`,`step`,`section`,`knowledgeone`,`knowledgetwo`,`appendtime` FROM  `question` ";
            if (recordlist!=null && recordlist.length>0){
                sql+=" WHERE `id` NOT IN (";
                for (String s : recordlist) {
                    sql+="?,";
                    params.add(s);
                }
                sql=sql.substring(0,sql.length()-1);//去掉最后一个，逗号
                sql+=" )";
            }
            if(section!=null){
                sql+=" AND`section`=?";
                params.add(section);
            }
            if(knowledgeone!=null){
                sql+=" AND `knowledgeone`=?";
                params.add(knowledgeone);
            }
            if(knowledgetwo!=null){
                sql+=" AND `knowledgetwo`=?";
                params.add(knowledgetwo);
            }
            sql+=" ORDER BY RAND() LIMIT ?";
            params.add(tmshu);
            rs = super.executeQuery(sql,params.toArray());
            while (rs.next()){
                Question question = new Question();
                question.setId(rs.getInt("id"));
                question.setContent(rs.getString("content"));
                question.setOption_a(rs.getString("option_a"));
                question.setOption_b(rs.getString("option_b"));
                question.setOption_c(rs.getString("option_c"));
                question.setOption_d(rs.getString("option_d"));
                question.setAnswer(rs.getString("answer"));
                question.setResolution(rs.getString("resolution"));
                question.setPraise(rs.getInt("praise"));
                question.setStep(rs.getInt("step"));
                question.setSection(rs.getInt("section"));
                question.setKnowledgeone(rs.getInt("knowledgeone"));
                question.setKnowledgetwo(rs.getInt("knowledgetwo"));
                question.setAppendtime(rs.getString("appendtime"));
                list.add(question);
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            super.closeAll(conn,null,rs);
        }
        return list;
    }


    public Record selectRecordlist(String section,String knowledgeone,String knowledgetwo){
        ResultSet rs = null;
        List<Object> params = new ArrayList<>();

        try {
            conn = super.getConnection();
            String sql="SELECT GROUP_CONCAT(`recordlist`SEPARATOR ', ') AS `recordlist`\n" +
                    "FROM `record` WHERE 1=1";
            if(section!=null){
                sql+=" AND`sid`=?";
                params.add(section);
            }
            if(knowledgeone!=null){
                sql+=" AND `knowledgeone`=?";
                params.add(knowledgeone);
            }
            if(knowledgetwo!=null){
                sql+=" AND `knowledgeid`=?";
                params.add(knowledgetwo);
            }
            rs = super.executeQuery(sql,params.toArray());
            while (rs.next()){
                Record record = new Record();
                record.setRecordlist(rs.getString("recordlist"));
                return record;
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            super.closeAll(conn,null,rs);
        }
        return null;
    }

    @Override
    public List<Question> selectQuestion1(String knowledgetwo,int tmshu) {
        List<Question> list = new ArrayList<>();
        ResultSet rs = null;
        List<Object> params = new ArrayList<>();
        try {
            String sql="SELECT `id` FROM  `question` where 1=1";
            if(knowledgetwo!=null){
                sql+=" AND `knowledgetwo`= ?";
                params.add(knowledgetwo);
            }
            sql+=" ORDER BY RAND() LIMIT ?";
            params.add(tmshu);
            rs = super.executeQuery(sql,params.toArray());
            while (rs.next()){
                Question question = new Question();
                question.setId(rs.getInt("id"));
                list.add(question);
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            super.closeAll(conn,null,rs);
        }
        return list;
    }

    public Question selectDaan(int answer){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = super.getConnection();
            stmt = conn.prepareStatement("SELECT `answer` FROM `question` WHERE `id`=?;");
            stmt.setInt(1,answer);
            rs = stmt.executeQuery();
            while (rs.next()){
                Question question = new Question();
                question.setAnswer(rs.getString("answer"));
                return question;
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            super.closeAll(conn,stmt,rs);
        }
        return null;
    }
    public int insertQuestion(Question question){
        String sql = "INSERT INTO `question` (`id`,`content`,`option_a`,`option_b`,`option_c`,`option_d`,`answer`,`resolution`,`praise`,`step`,`section`,`knowledgeone`,`knowledgetwo`,`appendtime`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,NOW())";
        return super.insertForId(sql, question.getId(), question.getContent(), question.getOption_a(), question.getOption_b(), question.getOption_c(), question.getOption_d(), question.getAnswer(), question.getResolution(), question.getPraise(), question.getStep(), question.getSection(), question.getKnowledgeone(), question.getKnowledgetwo());
    }
    public Question selectTyp(int id){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = super.getConnection();
            stmt = conn.prepareStatement("SELECT `id`,`content`,`option_a`,`option_b`,`option_c`,`option_d`,`answer`,`resolution`,\n" +
                    "`praise`,`step`,`section`,`knowledgeone`,`knowledgetwo`,`appendtime` FROM `question` WHERE `id`=?");
            stmt.setInt(1,id);
            rs = stmt.executeQuery();
            while (rs.next()){
                Question question = new Question();
                question.setId(rs.getInt("id"));
                question.setContent(rs.getString("content"));
                question.setOption_a(rs.getString("option_a"));
                question.setOption_b(rs.getString("option_b"));
                question.setOption_c(rs.getString("option_c"));
                question.setOption_d(rs.getString("option_d"));
                question.setAnswer(rs.getString("answer"));
                question.setResolution(rs.getString("resolution"));
                question.setPraise(rs.getInt("praise"));
                question.setStep(rs.getInt("step"));
                question.setSection(rs.getInt("section"));
                question.setKnowledgeone(rs.getInt("knowledgeone"));
                question.setKnowledgetwo(rs.getInt("knowledgetwo"));
                question.setAppendtime(rs.getString("appendtime"));
                return question;
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            super.closeAll(conn,stmt,rs);
        }
        return null;
    }

    public int selectTmshul(int knowledgetwo){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = super.getConnection();
            stmt = conn.prepareStatement("SELECT COUNT(*) AS `tmshul` FROM `question` WHERE `knowledgetwo` = ?");
            stmt.setInt(1,knowledgetwo);
            rs = stmt.executeQuery();
            while (rs.next()){
                int tmshul = rs.getInt("tmshul");
                return tmshul;
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            super.closeAll(conn,stmt,rs);
        }
        return 0;
    }
    public Question selectAll(int id){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = super.getConnection();
            stmt = conn.prepareStatement("SELECT `id`,`content`,`option_a`,`option_b`,`option_c`,`option_d`,`answer`,`resolution`,\n" +
                    "`praise`,`step`,`section`,`knowledgeone`,`knowledgetwo`,`appendtime` FROM `question` WHERE `id`=?");
            stmt.setInt(1,id);
            rs = stmt.executeQuery();
            while (rs.next()){
                Question question = new Question();
                question.setId(rs.getInt("id"));
                question.setContent(rs.getString("content"));
                question.setOption_a(rs.getString("option_a"));
                question.setOption_b(rs.getString("option_b"));
                question.setOption_c(rs.getString("option_c"));
                question.setOption_d(rs.getString("option_d"));
                question.setAnswer(rs.getString("answer"));
                question.setResolution(rs.getString("resolution"));
                question.setPraise(rs.getInt("praise"));
                question.setStep(rs.getInt("step"));
                question.setSection(rs.getInt("section"));
                question.setKnowledgeone(rs.getInt("knowledgeone"));
                question.setKnowledgetwo(rs.getInt("knowledgetwo"));
                question.setAppendtime(rs.getString("appendtime"));
                return question;
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            super.closeAll(conn,stmt,rs);
        }
        return null;
    }

}
